using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using QLNhaSach.DAO;
using QLNhaSach.BUS.DTO;

namespace QLNhaSach.DAO
{
    public class NhanVienDAO
    {
        // Hien thi danh sanh nhan vien
        public static DataTable DSNV()
        {
            string sql = "SELECT * FROM NhanVien";
            return Config.AccessHelper.ExecuteQuery(sql);
        }

        // Thay doi thong tin lien lac va mat khau nhan vien
        public static bool ThayDoiTTNV(NhanVienDTO user)
        {
            try
            {
                string sql="UPDATE NhanVien SET DiaChi = '"+ user.DiaChi +"', DienThoai = '"+user.DienThoai+"', [Password] = '";
                sql+= user.PassWord+"' WHERE (MaNV = "+user.MaNV+")";                   
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }


        // Load thong tin cua chinh nhan vien dang dang nhap
        public static DataTable  HienThiTTCN(NhanVienDTO nv)
        {
            string sql = "SELECT * FROM NhanVien where MaNV="+nv.MaNV;
             return Config.AccessHelper.ExecuteQuery(sql);
        }

        // Tao mot tai khoan nhan vien moi
        public static bool TaoTaiKhoanNV(NhanVienDTO nv)
        {
            try
            {
                //tao cau truy van va thuc thi cau truy van
                string sql = "INSERT INTO NHANVIEN(HoTen,[Password],PhanQuyen,TinhTrang,DiaChi,DienThoai,GioiTinh,NgaySinh)";
                sql += "VALUES('" + nv.HoTen + "','" + nv.PassWord + "'," + nv.PhanQuyen + "," + 1 + ",'" + nv.DiaChi;
                sql += "','" + nv.DienThoai + "'," + nv.GioiTinh + ",'" + nv.NgaySinh + "')";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //Lay MaNV dua vao HoTen,Password,tinh trang, phan quyen
        public static DataTable LayMaNV(NhanVienDTO nv)
        {
                string sql = "SELECT MaNV FROM NhanVien WHERE (HoTen = '" + nv.HoTen + "') AND ([Password] = '" + nv.PassWord + "')";
                sql += "AND (PhanQuyen = " + nv.PhanQuyen + ") AND (TinhTrang = 1)";
                return Config.AccessHelper.ExecuteQuery(sql);          
        }

        //Tao username cho nhan vien vua duoc tao
        public static bool CapNhapUserName(NhanVienDTO nv)
        {
            try
            {
                string sql = "UPDATE NhanVien SET Username = '" + nv.MaNV.ToString();
                sql += "' WHERE (MaNV = " + nv.MaNV + ")";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        // Cap nhat thong tin cho mot tai khoan nhan vien
        public static bool CapNhatTaiKhoanNV(NhanVienDTO nv)
        {
            try
            {
                string sql = "UPDATE NhanVien SET DiaChi='" + nv.DiaChi + "',DienThoai='" + nv.DienThoai + "',Username='" + nv.UserName + "',NgaySinh = '" + nv.NgaySinh + "',PhanQuyen=" + nv.PhanQuyen + ",GioiTinh=" + nv.GioiTinh + " where (MaNV=" + nv.MaNV + ")";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }

        //Tim kiem nhan vien dua vao username va password
        public static DataTable DangNhap(NhanVienDTO nv)
        {
            string sql = "SELECT * FROM NhanVien where username = '" + nv.UserName + "' and password = '" + nv.PassWord + "'";
            DataTable dt = Config.AccessHelper.ExecuteQuery(sql);
            return dt;
        }

        //Cap nhap phan quyen va tinh trang cua nhan vien
        public static bool CapNhapPhanQuyen(NhanVienDTO nv)
        {
            try
            {
                string sql = "UPDATE NhanVien SET PhanQuyen = " + nv.PhanQuyen + ", TinhTrang = " + nv.TinhTrang;
                sql += " WHERE (MaNV = " + nv.MaNV + ")";
                Config.AccessHelper.ExecuteNonQuery(sql);
                return true;
            }
            catch
            {
                return false;
            }
        }
    }
}
